﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using FU.Capstones.IMS.Services.Common;
using FU.Capstones.IMS.Services.Common.DataContracts;
using FU.Capstones.IMS.Services.Common.Respondents;
using FU.Capstones.IMS.Services.DataContracts;
using FU.Capstones.IMS.Services.Interfaces;

namespace FU.Capstones.IMS.Services.DAOs
{
    internal class ProductDAO : DAOBase, IProductDAO
    {
        #region IProductDAO Members

        public CollectionFeedback<Product> GetCollection(bool isShowAll)
        {
            try
            {
                var command = new SqlCommand("GetCollectionProduct", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@IsShowAll", isShowAll);

                var dt = new DataTable("AllProduct");
                var sqlda = new SqlDataAdapter(command);
                sqlda.Fill(dt);
                var list = new List<Product>();
                if (dt.Rows.Count != 0)
                {
                    foreach (DataRow dataRow in dt.Rows)
                    {
                        var warehouse = new Warehouse();
                        if (dataRow["WarehouseID"] != DBNull.Value)
                        {
                            warehouse.WarehouseID = Convert.ToInt32(dataRow["WarehouseID"]);
                            warehouse.WarehouseName = Convert.ToString(dataRow["WarehouseName"]);
                        }
                        var result = new Product();
                        result.Warehouse = warehouse;
                        result.ProductID = Convert.ToInt32(dataRow["ProductID"]);
                        result.ProductName = Convert.ToString(dataRow["ProductName"]);
                        result.ProductCode = Convert.ToString(dataRow["ProductCode"]);
                        if (dataRow["Quantity"] != DBNull.Value)
                        {
                            result.Quantity = Convert.ToInt32(dataRow["Quantity"]);
                        }
                        else
                        {
                            result.Quantity = 0;
                        }
                        if (dataRow["Description"] != DBNull.Value)
                        {
                            result.Description = Convert.ToString(dataRow["Description"]);
                        }
                        result.IsDelete = Convert.ToBoolean(dataRow["IsDelete"]);
                        list.Add(result);
                    }
                }
                return new CollectionFeedback<Product>(InventoryConstants.Messages.GetSuccessfully, true, list);
            }
            catch (SqlException e)
            {
                return new CollectionFeedback<Product>(InventoryConstants.Messages.GetFailed, false, null);
            }
            catch (Exception e)
            {
                return new CollectionFeedback<Product>(InventoryConstants.Messages.GetFailed, false, null);
            }
            finally
            {
                CloseConnection();
            }
        }

        public CollectionFeedback<Product> GetCollectionType(bool isShowAll)
        {
            try
            {
                var command = new SqlCommand("GetCollectionProductType", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@IsShowAll", isShowAll);

                var dt = new DataTable("AllProduct");
                var sqlda = new SqlDataAdapter(command);
                sqlda.Fill(dt);
                var list = new List<Product>();
                if (dt.Rows.Count != 0)
                {
                    foreach (DataRow dataRow in dt.Rows)
                    {
                        var result = new Product();
                        result.ProductID = Convert.ToInt32(dataRow["ProductID"]);
                        result.ProductName = Convert.ToString(dataRow["ProductName"]);
                        result.ProductCode = Convert.ToString(dataRow["ProductCode"]);
                        if (dataRow["Description"] != DBNull.Value)
                        {
                            result.Description = Convert.ToString(dataRow["Description"]);
                        }
                        result.IsDelete = Convert.ToBoolean(dataRow["IsDelete"]);
                        list.Add(result);
                    }
                }
                return new CollectionFeedback<Product>(InventoryConstants.Messages.GetSuccessfully, true, list);
            }
            catch (SqlException e)
            {
                return new CollectionFeedback<Product>(InventoryConstants.Messages.GetFailed, false, null);
            }
            catch (Exception e)
            {
                return new CollectionFeedback<Product>(InventoryConstants.Messages.GetFailed, false, null);
            }
            finally
            {
                CloseConnection();
            }
        }

        public SingleFeedback<Product> GetSingle(int objectID)
        {
            var feedback = new SingleFeedback<Product> {IsSuccessful = false};

            try
            {
                var command = new SqlCommand("GetSingleProduct", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@productID", objectID);

                OpenConnection();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var product = new Product();

                        product.ProductID = Convert.ToInt32(reader["ProductID"]);
                        product.ProductCode = Convert.ToString(reader["ProductCode"]);
                        product.ProductName = Convert.ToString(reader["ProductName"]);
                        if (reader["Description"] != DBNull.Value)
                        {
                            product.Description = Convert.ToString(reader["Description"]);
                        }
                        product.IsDelete = Convert.ToBoolean(reader["IsDelete"]);
                        feedback.Data = product;

                        break;
                    }

                    if (reader.NextResult())
                    {
                        feedback.Data.MaterialCollection = new List<Material>();
                        while (reader.Read())
                        {
                            var material = new Material();

                            material.MaterialID = Convert.ToInt32(reader["MaterialID"]);
                            material.MaterialCode = Convert.ToString(reader["MaterialCode"]);
                            material.MaterialName = Convert.ToString(reader["MaterialName"]);
                            if (reader["Quantity"] != DBNull.Value)
                            {
                                material.Quantity = Convert.ToInt32(reader["Quantity"]);
                            }
                            if (reader["Description"] != DBNull.Value)
                            {
                                material.Description = Convert.ToString(reader["Description"]);
                            }
                            feedback.Data.MaterialCollection.Add(material);
                        }
                    }
                    feedback.Message = InventoryConstants.Messages.GetSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.NA;
                    feedback.IsSuccessful = true;
                    feedback.Data = null;
                }

                Log.Info("GetSingleProduct: {0} - {1}", objectID, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("GetSingleProduct - SqlException: {0} - {1}", se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("GetSingleProduct - Exception: {0} - {1}", e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public SingleFeedback<int> Insert(Product data)
        {
            var feedback = new SingleFeedback<int>();
            try
            {
                var command = new SqlCommand("InsertProduct", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@ProductCode", data.ProductCode);
                command.Parameters.AddWithValue("@ProductName", data.ProductName);
                command.Parameters.AddWithValue("@Description", data.Description);
                command.Parameters.AddWithValue("@BuiltupStrutures", GetBuitupStructuresString(data.MaterialCollection));

                command.Parameters.Add("@ProductID", SqlDbType.Int);
                command.Parameters["@ProductID"].Direction = ParameterDirection.Output;

                OpenConnection();

                command.ExecuteNonQuery();

                var productID = (int) command.Parameters["@ProductID"].Value;
                if (productID > 0)
                {
                    feedback.Data = productID;
                    feedback.Message = InventoryConstants.Messages.InsertSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.InsertFailed;
                    feedback.IsSuccessful = false;
                    feedback.Data = -1;
                }

                Log.Info("InsertProduct: {0} - {1}", feedback.Data, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("InsertProduct - SqlException: {0} - {1}", se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("InsertProduct - Exception: {0} - {1}", e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback InsertBuiltupMaterial(int pID, int mID, long mQuantity)
        {
            var feedback = new Feedback();
            try
            {
                var command = new SqlCommand("InsertBuiltupMaterial", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@MaterialID", mID);
                command.Parameters.AddWithValue("@ProductID", pID);
                command.Parameters.AddWithValue("@Quantity", Convert.ToInt32(mQuantity));
                //command.Parameters.AddWithValue("@BuiltupStrutures", GetBuitupStructuresString(data.MaterialCollection));

                OpenConnection();


                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    feedback.Message = InventoryConstants.Messages.InsertSuccessfully;
                    feedback.IsSuccessful = true;
                }


                //Log.Info("InsertProduct: {0} - {1}", feedback.Data, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("InsertBuiltupMaterial - SqlException: {0} - {1}", se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("InsertBuiltupMaterial - Exception: {0} - {1}", e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback Update(Product data)
        {
            var feedback = new Feedback();
            try
            {
                //create a new sqlcommand 
                var command = new SqlCommand("UpdateProduct", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                //
                command.Parameters.AddWithValue("@ProductID", data.ProductID);
                command.Parameters.AddWithValue("@ProductCode", data.ProductCode);
                command.Parameters.AddWithValue("@ProductName", data.ProductName);
                command.Parameters.AddWithValue("@Description", data.Description);
                command.Parameters.AddWithValue("@BuiltupStrutures", GetMaterialLoafString(data.MaterialCollection));

                OpenConnection();

                int rowCount = command.ExecuteNonQuery();

                if (rowCount >= 1)
                {
                    feedback.Message = InventoryConstants.Messages.UpdateSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.UpdateFailed;
                    feedback.IsSuccessful = false;
                }

                Log.Info("UpdateProduct: {0} - {1} - {2}", data.ProductID, feedback.Message, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("UpdateProduct - SqlException: {0} - {1} - {2}", data.ProductID, se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("DeleteProduct - Exception: {0} - {1} - {2}", data.ProductID, e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback Delete(int objID)
        {
            var feedback = new Feedback();
            try
            {
                var command = new SqlCommand("DeleteProduct", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@ProductID", objID);

                OpenConnection();

                int rowCount = command.ExecuteNonQuery();
                if (rowCount == 1)
                {
                    feedback.Message = InventoryConstants.Messages.DeleteSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.DeleteFailed;
                    feedback.IsSuccessful = false;
                }

                Log.Info("DeleteProduct: {0} - {1} - {2}", objID, feedback.Message, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("DeleteProduct - SqlException: {0} - {1} - {2}", objID, se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("DeleteProduct - Exception: {0} - {1} - {2}", objID, e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback DeleteBuiltup(int pID, int mID)
        {
            var feedback = new Feedback();
            try
            {
                var command = new SqlCommand("DeleteBuiltup", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@ProductID", pID);
                command.Parameters.AddWithValue("@MaterialID", mID);

                OpenConnection();

                int rowCount = command.ExecuteNonQuery();
                if (rowCount == 1)
                {
                    feedback.Message = InventoryConstants.Messages.DeleteSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.DeleteFailed;
                    feedback.IsSuccessful = false;
                }

                Log.Info("DeleteBuiltup: {0} - {1} - {2}", pID, feedback.Message, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("DeleteBuiltup - SqlException: {0} - {1} - {2}", pID, se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("DeleteBuiltup - Exception: {0} - {1} - {2}", pID, e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public CollectionFeedback<Product> Search(Product criteria)
        {
            var feedback = new CollectionFeedback<Product> {IsSuccessful = false, Data = new List<Product>()};
            var pCriteria = (ProductCriteria) criteria;

            try
            {
                OpenConnection();

                var command = new SqlCommand("SearchProduct", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@ProductCode",
                                                String.IsNullOrEmpty(pCriteria.ProductCode)
                                                    ? pCriteria.ProductCode
                                                    : pCriteria.ProductCode.Trim());
                command.Parameters.AddWithValue("@ProductName",
                                                String.IsNullOrEmpty(pCriteria.ProductName)
                                                    ? pCriteria.ProductName
                                                    : pCriteria.ProductName.Trim());
                command.Parameters.AddWithValue("@TotalQuantityFrom", pCriteria.TotalQuantityFrom);
                command.Parameters.AddWithValue("@TotalQuantityTo", pCriteria.TotalQuantityTo);
                if (pCriteria.WarehouseIDCollection != null && pCriteria.WarehouseIDCollection.Count > 0)
                {
                    command.Parameters.AddWithValue("@WarehouseID", pCriteria.WarehouseIDCollection[0]);
                }


                SqlDataReader reader = command.ExecuteReader();

                var products = new List<Product>();

                while (reader.Read())
                {
                    var warehouse = new Warehouse();
                    warehouse.WarehouseID = Convert.ToInt32(reader["WarehouseID"]);
                    warehouse.WarehouseName = Convert.ToString(reader["WarehouseName"]);

                    var product = new Product();
                    product.WarehouseID = warehouse.WarehouseID;
                    product.Warehouse = warehouse;
                    product.ProductID = Convert.ToInt32(reader["ProductID"]);
                    product.ProductCode = Convert.ToString(reader["ProductCode"]);
                    product.ProductName = Convert.ToString(reader["ProductName"]);
                    product.Quantity = Convert.ToInt32(reader["Quantity"]);
                    product.Description = Convert.ToString(reader["Description"]);
                    product.IsDelete = Convert.ToBoolean(reader["IsDelete"]);

                    products.Add(product);
                }
                feedback.Message = InventoryConstants.Messages.SearchSuccessfully;
                feedback.IsSuccessful = true;
                feedback.Data = products;


                Log.Info("SearchProduct: {0}, {1}", feedback.Message, DateTime.Now.ToString());
            }
            catch (SqlException e)
            {
                Log.Error(e.Message);
                feedback.Message = InventoryConstants.Messages.SearchFailed;
            }
            catch (Exception t)
            {
                Log.Error(t.Message);
                feedback.Message = InventoryConstants.Messages.SearchFailed;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        public Feedback UndoDelete(int objID)
        {
            var feedback = new Feedback();
            try
            {
                var command = new SqlCommand("UndoDeletedProduct", imsConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@ProductID", objID);

                OpenConnection();

                int rowCount = command.ExecuteNonQuery();
                if (rowCount == 1)
                {
                    feedback.Message = InventoryConstants.Messages.UndoDeleteSuccessfully;
                    feedback.IsSuccessful = true;
                }
                else
                {
                    feedback.Message = InventoryConstants.Messages.UndoDeleteFailed;
                    feedback.IsSuccessful = false;
                }

                Log.Info("UndoDeletedProduct: {0} - {1} - {2}", objID, feedback.Message, DateTime.Now);
            }
            catch (SqlException se)
            {
                Log.Error("UndoDeletedProduct - SqlException: {0} - {1} - {2}", objID, se.StackTrace, DateTime.Now);
                feedback.Message = se.Message;
            }
            catch (Exception e)
            {
                Log.Error("UndoDeletedProduct - Exception: {0} - {1} - {2}", objID, e.StackTrace, DateTime.Now);
                feedback.Message = e.Message;
            }
            finally
            {
                CloseConnection();
            }
            return feedback;
        }

        #endregion
    }
}